Data file considerations

When working with data files, one-to-many links can occur when you link tables in a single report or when you add a subreport to your report.

Linking data files

When retrieving data from linked data files in one-to-many situations, the program uses the following process:

• If there is a selection formula, the program parses the selection formula and passes what it can down to the database DLL. This is generally range limit information. Consider the following record selection formula:

  {customer.REGION} in "CA" to "IL" AND
  Remainder ({customer.CUSTOMER ID},2)=0

In this formula, the part before the "and" operator contains range selection criteria for the Region field. The region must fall alphabetically between "CA" and "IL." The program passes this kind of condition down to either the database DLL (for PC data) or the server (for SQL data). See Record Selection.

The second half of the selection formula, however, requires processing that must be done in the Report Engine. It uses a built-in function to manipulate and evaluate a field value and it cannot be done in the database DLL or the server. The program does not pass this condition to the database DLL.

• If there is an index on Table A, and the range limit selection condition is based on the indexed field ({customer.REGION} in this example), the program goes directly to the record it is seeking in Table A (the first CA record) and reads it.
  • For that record, the program locates the first matching record in Table B, using the Table B index.
  • The program passes this merged record (A+B) back to the Report Designer, which tests the record against the entire selection formula.
  • The program then reads the second matching record and passes the merged record on, and then reads the third matching record, and so on, until it has read all of the matching records.
  • The program then returns to Table A and reads the next record. There is no need to test the record to see if it meets the CA condition; the field is indexed and the records are in alphabetic order. But the program tests the record to see if it goes beyond the "IL" condition (for example, could the next record be from Mississippi or Tennessee?). If the record is still within the specified range, the program begins the matching process again for that record.
  • The program continues the process until it has located all targeted Table A records and the matching Table B records.

    To find two records in Table A and the 100 records in Table B that match the Table A records, the program reads 200 records.

Note:    The lookup table in a link (Table B) must always be indexed; otherwise, you will not be able to link the tables.

• If there is no index on Table A, or if there is an index but the range limit selection condition is not based on the indexed field, the program reads the first record it finds.
  • For that record, the program uses the Table B index to locate the first matching record in Table B.
  • The program passes this merged record (A+B) back to the Report Engine, which tests it against the entire selection formula.
  • The program then locates the second matching record in Table B and passes that merged record back, then the third record, and so on, until it has located, merged, and passed back all the records in Table B that match the first record in Table A.
  • The program then moves on to the next record in Table A and begins the matching and merging process all over again.

    To find two records in Table A and the 100 records in Table B that match the Table A records, the program reads 2600 records.

Subreports and data files

If your primary report is based on Table A, the subreport is based on Table B, and the records are linked, your primary considerations are as follows:

• The number of subreports that are run by the program is determined by the index and the selection formula situation in the primary report.
  • If Table A is indexed, and if the primary report has a selection formula that passes down range limit conditions for the indexed field, the program runs two subreports.
  • If Table A is not indexed, or if Table A is indexed but the selection formula does not pass down range limit conditions for the indexed field, the program runs 26 subreports.
• The number of records read for each subreport is determined by the index situation on Table B.
  • If you have an index on Table B, the program will read only the matching records (100) when it runs a subreport.
  • If you do not have an index on Table B, the program will always read every record in Table B (2600) when it runs a subreport.